﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Model;
namespace DAL
{
   public  class DALchaALLhy
    {
       //查所有会员
       public static SqlDataReader chaAllHy() {
           string sql = @"select VipID,VipName,VipSex,VGName,VGDiscount,VipTel,VipStartDate,VipEndDate from dbo.Vips as aa
inner join dbo.VIPGrade as bb
on aa.GradeID=bb.VGID";
         return  DBHelper.Zhilianshi(sql,null);
       }
       //根据id或者姓名查会员
       public static SqlDataReader chaHYbyid() {
           string sql =@"select VipID,VipName,VipSex,VGName,VGDiscount,VipTel,VipStartDate,VipEndDate from dbo.Vips as aa
inner join dbo.VIPGrade as bb
on aa.GradeID=bb.VGID where 1=1";
            List<SqlParameter> list = new List<SqlParameter>();
           if(Model_Vips.VipID!=""){
               sql += @" and VipID=@VipID";
               list.Add(new SqlParameter("@VipID",Model_Vips.VipID));
           }
           if(Model_Vips.VipName!=""){
               sql += @" and VipName=@VipName";
               list.Add(new SqlParameter("@VipName", Model_Vips.VipName));
           }
           return DBHelper.Zhilianshi(sql, list.ToArray());
       
       }
       //查cbbox框
       public static DataSet chaHUIyuanDengji() {
           string sql = "select VGID,VGName from VipGrade";
          DataSet ds= DBHelper.Duankaishi(sql,null);
          return ds;
       }
       //添加会员
       public static int TJvip(){
            string sql="insert into dbo.vips values(@VipName,@VipSex,@GradeID,@VipTel,GETDATE(),@VipEndDate)";
            List<SqlParameter> list = new List<SqlParameter> { 
               new SqlParameter("@VipName",Model_Vips.VipName),
               new SqlParameter("@VipSex",Model_Vips.VipSex),
               new SqlParameter("@GradeID",Model_Vips.GradeID),
               new SqlParameter("@VipTel",Model_Vips.VipTel),
               new SqlParameter("@VipEndDate",Model_Vips.VipEndDate),
            };
         int a=  DBHelper.Update(sql,list.ToArray());
         return a;
    }
       //删除会员
       public static int SCvip() {
           string sql = "delete  dbo.Vips where VipID=@VipID";
           List<SqlParameter> list = new List<SqlParameter>
       {
       new SqlParameter("@VipID",Model_Vips.VipID),
       };
          return DBHelper.Update(sql,list.ToArray());
       }
       //修改会员
       public static int XGvip()
       {
           string sql = "update Vips set vipName=@vipName ,VipSex=@VipSex ,GradeID=@GradeID,VipTel=@VipTel,VipEndDate=@VipEndDate  where  VipID=@VipID";
           List<SqlParameter> list = new List<SqlParameter> { 
           new SqlParameter("@vipName ",Model_Vips.VipName),
            new SqlParameter("@VipSex ",Model_Vips.VipSex),
            new SqlParameter("@GradeID",Model_Vips.GradeID),
            new SqlParameter("@VipTel",Model_Vips.VipTel),
            new SqlParameter("@VipEndDate ",Model_Vips.VipEndDate),
            new SqlParameter("@VipID ",Model_Vips.VipID),
          
         
           };
           return DBHelper.Update(sql, list.ToArray());
       }
       //查看消费记录 
       public static SqlDataReader CKvipXF() {
           string sql = @"
select CBID,ProductName,CDAmount,ProductPrice,CDMoney,CDDate from dbo.ConsumerDetail as c
join Products as p
on
c.ProdcutID=p.ProductID
where CBID in(select CBID from dbo.ConsumerBill where VipID=@VipID)";
           List<SqlParameter> list = new List<SqlParameter> { 
           new SqlParameter("@VipID",Model_Vips.VipID),
           };
           return DBHelper.Zhilianshi(sql,list.ToArray());
       }
    }
}
